﻿Imports DTO
Imports System.Data.SqlClient

Public Class RoomDAO
    Public Function GetListRoom() As List(Of RoomDTO)
        Dim listResult As New List(Of RoomDTO)
        Try
            Dim sql As String = "select RoomID, isNull(Price,0) Price, isNull(Type,'') Type, isNull(Capacity,'') Capacity, isNull(Status,'') Status from Room  order by Price "
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim room As New RoomDTO
                room.RoomID = reader("RoomID")
                room.Price = reader("Price")
                room.Type = reader("Type")
                room.Capacity = reader("Capacity")
                room.Status = reader("Status")
                listResult.Add(room)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

    Public Sub Update(ByVal roomId As Integer, ByVal status As String)
        Try
            Dim sSQL As String = String.Format("UPDATE Room SET Status='{0}' WHERE RoomID={1}", status, roomId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            Throw ex
        End Try
    End Sub

    Public Function GetListRoomByBookingId(ByVal bookingId As Integer) As List(Of RoomDTO)
        Dim listResult As New List(Of RoomDTO)
        Try
            Dim sql As String = String.Format("select r.RoomID, isNull(r.Price,0) Price from Room r, Bookroom br where r.RoomId=br.RoomId and br.BookingId={0}  order by Price ", bookingId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim room As New RoomDTO
                room.RoomID = reader("RoomID")
                room.Price = reader("Price")    
                listResult.Add(room)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function
End Class
